﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VLIEFDAT
    {
        public void SetUpNewRow(ref DataRow dwReturn, DataRow dwInput)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                default:
                    #region=========
                    dwReturn["LSMANDANT"] = app.Mandant;
                    dwReturn["LSLIEFNR"] = "00000013";
                    dwReturn["LSLIEFNA"] = "          ";
                    dwReturn["LSKREDNR"] = "00000013";
                    dwReturn["LSARTGR"] = "1";
                    dwReturn["LSGRSCHL"] = "TS ";
                    dwReturn["LSSTAKZ"] = "A";
                    dwReturn["LSQUALKZ"] = " ";
                    dwReturn["LSSPRACHKZ"] = "V";
                    dwReturn["LSANREDE"] = "3";
                    dwReturn["LSNAME1"] = "                                        ";
                    dwReturn["LSNAME2"] = "                                        ";
                    dwReturn["LSSTRASSE1"] = "                                        ";
                    dwReturn["LSSTRASSE2"] = "                                        ";
                    dwReturn["LSLAND"] = "CN ";
                    dwReturn["LSPLZ"] = "            ";
                    dwReturn["LSORT"] = "                                        ";
                    dwReturn["LSPOSTF"] = "               ";
                    dwReturn["LSPLZPOSTF"] = "            ";
                    dwReturn["LSCOUNTY"] = "    ";
                    dwReturn["LSCITY"] = "    ";
                    dwReturn["LSTEL"] = "                    ";
                    dwReturn["LSTELEX"] = "                    ";
                    dwReturn["LSFAX"] = "                    ";
                    dwReturn["LSBTX"] = "                                        ";
                    dwReturn["LSMOBIL"] = "                    ";
                    dwReturn["LSMAILADR"] = "                                        ";
                    dwReturn["LSBEM"] = "                                        ";
                    dwReturn["LSAPNA1"] = "                                        ";
                    dwReturn["LSAPBEZ1"] = "                                        ";
                    dwReturn["LSAPTEL1"] = "                    ";
                    dwReturn["LSAPNA2"] = "                                        ";
                    dwReturn["LSAPBEZ2"] = "                                        ";
                    dwReturn["LSAPTEL2"] = "                    ";
                    dwReturn["LSADRV1STD"] = "        ";
                    dwReturn["LSADRV2STD"] = "        ";
                    dwReturn["LSADRRUECKSTD"] = "        ";
                    dwReturn["LSKONDSLSTD"] = "STD ";
                    dwReturn["LSEGKZ"] = "N";
                    dwReturn["LSWAEHRSL"] = "RMB";
                    dwReturn["LSKDNRLIEF"] = "          ";
                    dwReturn["LSFRAGRE"] = "0";
                    dwReturn["LSMINBST"] = "0";
                    dwReturn["LSLO"] = "  ";
                    dwReturn["LSLB"] = "  ";
                    dwReturn["LSLP"] = "        ";
                    dwReturn["LSDEBNR"] = "00000000";
                    dwReturn["LSJJJJ1"] = "0";
                    dwReturn["LSUMSATZ1"] = "00000000000";
                    dwReturn["LSGUTSCH1"] = "00000000000";
                    dwReturn["LSJJJJ2"] = "0";
                    dwReturn["LSUMSATZ2"] = "00000000000";
                    dwReturn["LSGUTSCH2"] = "00000000000";
                    dwReturn["LSJJJJ3"] = "0";
                    dwReturn["LSUMSATZ3"] = "00000000000";
                    dwReturn["LSGUTSCH3"] = "00000000000";
                    dwReturn["LSSALDO"] = "00000000000";
                    dwReturn["LSOBLIGO"] = "00000000000";
                    dwReturn["LSKREDLIM"] = "0";
                    dwReturn["LSVERSNR"] = "          ";
                    dwReturn["LSABCKL"] = " ";
                    dwReturn["LSRECHBEM"] = "                                        ";
                    dwReturn["LSLIEFDAT"] = "20150319";
                    dwReturn["LSERINTAG"] = "0";
                    dwReturn["LSERINSL"] = "  ";
                    dwReturn["LSERINTXT"] = "     ";
                    dwReturn["LSMAHNTAG1"] = "10";
                    dwReturn["LSMAHNTXT1"] = "MA110";
                    dwReturn["LSMAHNTAG2"] = "15";
                    dwReturn["LSMAHNTXT2"] = "     ";
                    dwReturn["LSMAHNTAG3"] = "30";
                    dwReturn["LSMAHNTXT3"] = "MA310";
                    dwReturn["LSUIDNR"] = "                    ";
                    dwReturn["LSUMSVVJH"] = "00000000000";
                    dwReturn["LSUMSVJH"] = "00000000000";
                    dwReturn["LSUMSLJH"] = "00000000000";
                    dwReturn["LSILN"] = "             ";
                    dwReturn["LSAENZL"] = "7";
                    dwReturn["LSAENB"] = "        ";
                    dwReturn["LSAEND"] = "0";
                    dwReturn["LSNEUB"] = "        ";
                    dwReturn["LSNEUD"] = "0";
                    dwReturn["LSSTEUER"] = "   ";
                    dwReturn["LSEORI"] = "                 ";
                    dwReturn["LSFILLER"] = "                                                 ";
                    #endregion
                    break;
            }
            INTEXDBC.MapData(true, ref dwReturn, dwInput);
        }
        public DataSet QueryLIEFDAT(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            DataSet STemp;
            // APZMANDANT  APZART APZVAR APZANRKRS APZALFDNR APZSTUFE
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM LIEFDAT WHERE 1=2";
                    SResult = Connect2Proxy(or1);
                    break;
                case 1:
                case 2:
                    or1.CommandText = "SELECT * FROM LIEFDAT WHERE   LSMANDANT=:LSMANDANT: AND LSLIEFNR=:LSLIEFNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 3:
                    or1.CommandText = "SELECT LSLIEFNR,LSLIEFNA,RTRIM(LSLIEFNA)||RTRIM(LSNAME1)  AS LSNAME1  FROM LIEFDAT WHERE  "
                    + "  LSMANDANT=:LSMANDANT: AND LSGRSCHL=:LSGRSCHL:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 4:
                    or1.CommandText = "SELECT DISTINCT LF.* FROM ABFOLGE FL RIGHT JOIN LIEFDAT LF  ON FL.ABFMANDANT=LSMANDANT AND FL.ABFLIEFNR=LSLIEFNR"
                        + " WHERE ABFMANDANT=:ABFMANDANT: AND ABFAUFKRS=:ABFAUFKRS: AND ABFAUFLFD=:ABFAUFLFD:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 5:
                    or1.CommandText = "SELECT DISTINCT LF.* FROM EKKOPF FL JOIN LIEFDAT LF ON FL.EKKMANDANT=LSMANDANT AND FL.EKKLIEFNR=LSLIEFNR"
             + " WHERE EKKMANDANT=:EKKMANDANT: AND EKKAUFKRS=:EKKAUFKRS: AND EKKAUFLFD=:EKKAUFLFD:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 6:
                    or1.CommandText = "SELECT LSLIEFNR,LSLIEFNA,LSNAME1 FROM LIEFDAT WHERE  "
                    + "  LSMANDANT=:LSMANDANT:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 7:
                    or1.CommandText = "SELECT DISTINCT LF.* FROM  LIEFDAT LF  WHERE LSLIEFNR IN ( SELECT NVL(B.EKALIEFNR2,A.EKKLIEFNR) FROM EKKOPF A,EKADR B "
             + " WHERE A.EKKMANDANT = B.EKAMANDANT(+) AND A.EKKAUFKRS = B.EKAAUFKRS(+)   AND A.EKKAUFLFD = B.EKAAUFLFD(+)  AND A.EKKMANDANT=:EKKMANDANT: AND A.EKKAUFKRS=:EKKAUFKRS: AND A.EKKAUFLFD=:EKKAUFLFD:) ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 8:
                    or1.CommandText = "SELECT * FROM LIEFDAT WHERE   LSMANDANT=:LSMANDANT: AND (LSNAME1 LIKE'%" + dwData["LSNAME1"].ToString().Trim() + "%'"
                         + " OR LSLIEFNA LIKE'%" + dwData["LSNAME1"].ToString().Trim() + "%')"
                        + dwData["LSLIEFNR"].ToString()
                        + " ORDER BY LSLIEFNR ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case -1:
                    or1.CommandText = "SELECT LSLIEFNR,LSLIEFNA,LSNAME1,'' AS KEY,'' AS DES,LSFRAGRE FROM LIEFDAT  order by lsliefna";
                    SResult = Connect2Proxy(or1);
                    break;
                case -2:
                    or1.CommandText = "SELECT * FROM EKKOPF WHERE  ROWNUM<2 AND EKKMANDANT='" + app.Mandant + "' AND EKKLIEFNR='" + dwData["LSLIEFNR"].ToString() + "'";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    STemp = Connect2Proxy(or1);
                    if (STemp.Tables[0].Rows.Count > 0)
                    {
                        STemp.Tables[0].TableName = "采购单";
                        return STemp;
                    }
                    or1.CommandText = "SELECT * FROM WASTAMM WHERE  ROWNUM<2 AND WAMANDANT='" + app.Mandant + "' AND WALIEFNR='" + dwData["LSLIEFNR"].ToString() + "'";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    STemp = Connect2Proxy(or1);
                    if (STemp.Tables[0].Rows.Count > 0)
                    {
                        STemp.Tables[0].TableName = "品种管理";
                        return STemp;
                    }
                    SResult = Connect2Proxy(or1);
                    break;
            }

            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QLSNAME(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT LSLIEFNR FROM LIEFDAT WHERE   LSMANDANT=:LSMANDANT: AND LSLIEFNA=:LSLIEFNA:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            return SResult;
        }
        public DataSet QLS99(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM LIEFDAT WHERE LSMANDANT=:LSMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void InsertLIEFDAT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            dwData["LSNEUD"] = date;
            dwData["LSNEUB"] = app.User;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "SXN":
                case "TST":
                default:
                    or1.CommandText = "INSERT INTO LIEFDAT ( LSMANDANT,LSLIEFNR,LSLIEFNA,LSKREDNR,LSARTGR,LSGRSCHL,LSSTAKZ,LSQUALKZ,LSSPRACHKZ,LSANREDE,LSNAME1,LSNAME2,LSSTRASSE1,LSSTRASSE2,LSLAND,LSPLZ,LSORT,LSPOSTF,LSPLZPOSTF,LSCOUNTY,LSCITY,LSTEL,LSTELEX,LSFAX,LSBTX,LSMOBIL,LSMAILADR,LSBEM,LSAPNA1,LSAPBEZ1,LSAPTEL1,LSAPNA2,LSAPBEZ2,LSAPTEL2,LSADRV1STD,LSADRV2STD,LSADRRUECKSTD,LSKONDSLSTD,LSEGKZ,LSWAEHRSL,LSKDNRLIEF,LSFRAGRE,LSMINBST,LSLO,LSLB,LSLP,LSDEBNR,LSJJJJ1,LSUMSATZ1,LSGUTSCH1,LSJJJJ2,LSUMSATZ2,LSGUTSCH2,LSJJJJ3,LSUMSATZ3,LSGUTSCH3,LSSALDO,LSOBLIGO,LSKREDLIM,LSVERSNR,LSABCKL,LSRECHBEM,LSLIEFDAT,LSERINTAG,LSERINSL,LSERINTXT,LSMAHNTAG1,LSMAHNTXT1,LSMAHNTAG2,LSMAHNTXT2,LSMAHNTAG3,LSMAHNTXT3,LSUIDNR,LSUMSVVJH,LSUMSVJH,LSUMSLJH,LSILN,LSAENZL,LSAENB,LSAEND,LSNEUB,LSNEUD,LSSTEUER,LSEORI,LSFILLER)"
                    + " VALUES (:LSMANDANT:,:LSLIEFNR:,:LSLIEFNA:,:LSKREDNR:,:LSARTGR:,:LSGRSCHL:,:LSSTAKZ:,:LSQUALKZ:,:LSSPRACHKZ:,:LSANREDE:,:LSNAME1:,:LSNAME2:,:LSSTRASSE1:,:LSSTRASSE2:,:LSLAND:,:LSPLZ:,:LSORT:,:LSPOSTF:,:LSPLZPOSTF:,:LSCOUNTY:,:LSCITY:,:LSTEL:,:LSTELEX:,:LSFAX:,:LSBTX:,:LSMOBIL:,:LSMAILADR:,:LSBEM:,:LSAPNA1:,:LSAPBEZ1:,:LSAPTEL1:,:LSAPNA2:,:LSAPBEZ2:,:LSAPTEL2:,:LSADRV1STD:,:LSADRV2STD:,:LSADRRUECKSTD:,:LSKONDSLSTD:,:LSEGKZ:,:LSWAEHRSL:,:LSKDNRLIEF:,:LSFRAGRE:,:LSMINBST:,:LSLO:,:LSLB:,:LSLP:,:LSDEBNR:,:LSJJJJ1:,:LSUMSATZ1:,:LSGUTSCH1:,:LSJJJJ2:,:LSUMSATZ2:,:LSGUTSCH2:,:LSJJJJ3:,:LSUMSATZ3:,:LSGUTSCH3:,:LSSALDO:,:LSOBLIGO:,:LSKREDLIM:,:LSVERSNR:,:LSABCKL:,:LSRECHBEM:,:LSLIEFDAT:,:LSERINTAG:,:LSERINSL:,:LSERINTXT:,:LSMAHNTAG1:,:LSMAHNTXT1:,:LSMAHNTAG2:,:LSMAHNTXT2:,:LSMAHNTAG3:,:LSMAHNTXT3:,:LSUIDNR:,:LSUMSVVJH:,:LSUMSVJH:,:LSUMSLJH:,:LSILN:,:LSAENZL:,:LSAENB:,:LSAEND:,:LSNEUB:,:LSNEUD:,:LSSTEUER:,:LSEORI:,:LSFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

                    break;
            }

            Connect2Proxy(or1);
        }
        public void DeleteLIEFDAT(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM LIEFDAT   WHERE LSMANDANT=:LSMANDANT: AND LSLIEFNR=:LSLIEFNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DLS00(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "DELETE FROM LIEFDAT   WHERE LSMANDANT=:LSMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            Connect2Proxy(or1);
        }
        public void UpdateLIEFDAT(DataRow dwData, int option = 1)
        {
            if (dwData != null)
            {
                dwData["LSAENB"] = app.User;
                dwData["LSAEND"] = date;
            }
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                    case "YGR":
                    case "SXN":
                    case "TST":
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE LIEFDAT SET LSMANDANT=:LSMANDANT:,LSLIEFNR=:LSLIEFNR:,LSLIEFNA=:LSLIEFNA:,LSKREDNR=:LSKREDNR:,LSARTGR=:LSARTGR:,LSGRSCHL=:LSGRSCHL:,LSSTAKZ=:LSSTAKZ:,LSQUALKZ=:LSQUALKZ:,LSSPRACHKZ=:LSSPRACHKZ:,LSANREDE=:LSANREDE:,LSNAME1=:LSNAME1:,LSNAME2=:LSNAME2:,LSSTRASSE1=:LSSTRASSE1:,LSSTRASSE2=:LSSTRASSE2:,LSLAND=:LSLAND:,LSPLZ=:LSPLZ:,LSORT=:LSORT:,LSPOSTF=:LSPOSTF:,LSPLZPOSTF=:LSPLZPOSTF:,LSCOUNTY=:LSCOUNTY:,LSCITY=:LSCITY:,LSTEL=:LSTEL:,LSTELEX=:LSTELEX:,LSFAX=:LSFAX:,LSBTX=:LSBTX:,LSMOBIL=:LSMOBIL:,LSMAILADR=:LSMAILADR:,LSBEM=:LSBEM:,LSAPNA1=:LSAPNA1:,LSAPBEZ1=:LSAPBEZ1:,LSAPTEL1=:LSAPTEL1:,LSAPNA2=:LSAPNA2:,LSAPBEZ2=:LSAPBEZ2:,LSAPTEL2=:LSAPTEL2:,LSADRV1STD=:LSADRV1STD:,LSADRV2STD=:LSADRV2STD:,LSADRRUECKSTD=:LSADRRUECKSTD:,LSKONDSLSTD=:LSKONDSLSTD:,LSEGKZ=:LSEGKZ:,LSWAEHRSL=:LSWAEHRSL:,LSKDNRLIEF=:LSKDNRLIEF:,LSFRAGRE=:LSFRAGRE:,LSMINBST=:LSMINBST:,LSLO=:LSLO:,LSLB=:LSLB:,LSLP=:LSLP:,LSDEBNR=:LSDEBNR:,LSJJJJ1=:LSJJJJ1:,LSUMSATZ1=:LSUMSATZ1:,LSGUTSCH1=:LSGUTSCH1:,LSJJJJ2=:LSJJJJ2:,LSUMSATZ2=:LSUMSATZ2:,LSGUTSCH2=:LSGUTSCH2:,LSJJJJ3=:LSJJJJ3:,LSUMSATZ3=:LSUMSATZ3:,LSGUTSCH3=:LSGUTSCH3:,LSSALDO=:LSSALDO:,LSOBLIGO=:LSOBLIGO:,LSKREDLIM=:LSKREDLIM:,LSVERSNR=:LSVERSNR:,LSABCKL=:LSABCKL:,LSRECHBEM=:LSRECHBEM:,LSLIEFDAT=:LSLIEFDAT:,LSERINTAG=:LSERINTAG:,LSERINSL=:LSERINSL:,LSERINTXT=:LSERINTXT:,LSMAHNTAG1=:LSMAHNTAG1:,LSMAHNTXT1=:LSMAHNTXT1:,LSMAHNTAG2=:LSMAHNTAG2:,LSMAHNTXT2=:LSMAHNTXT2:,LSMAHNTAG3=:LSMAHNTAG3:,LSMAHNTXT3=:LSMAHNTXT3:,LSUIDNR=:LSUIDNR:,LSUMSVVJH=:LSUMSVVJH:,LSUMSVJH=:LSUMSVJH:,LSUMSLJH=:LSUMSLJH:,LSILN=:LSILN:,LSAENZL=:LSAENZL:,LSAENB=:LSAENB:,LSAEND=:LSAEND:,LSNEUB=:LSNEUB:,LSNEUD=:LSNEUD:,LSSTEUER=:LSSTEUER:,LSEORI=:LSEORI:,LSFILLER=:LSFILLER:"
                        + " WHERE LSMANDANT=:LSMANDANT: AND LSLIEFNR=:LSLIEFNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
